﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml;
using System.Web.UI.DataVisualization.Charting;
using System.Drawing;
using WebSite.UserTest;
using System.Collections;
using System.Collections.Generic;


public partial class WebSite_t_data_contrast : System.Web.UI.Page
{
    //存放搜索数据所需信息的类
    class SearchInfo 
    {
        public string Province;    //省份
        public string Sex;         //性别
        public string Testyear;    //测试年份
        public string Grade;       //年级
        public string College;     //学院
        public string Target;      //测试项目
        public string Nationality; //民族

        public int count;           //本组总数量
        public Dictionary<string, int> LevelMemery; //等级和数量对应表
        public SearchInfo() { LevelMemery = new Dictionary<string, int>(); count = 0; }
    };

    //搜索数据所需的信息
    SearchInfo[] search ;
    SqlConnection conn;
    public WebSite_t_data_contrast()
    {
        search=new SearchInfo[2];
        search[0]=new SearchInfo();
        search[1]=new SearchInfo();
        conn = new SqlConnection("Data Source=Localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
        conn.Open();
    }

    protected void Page_Load(object sender, EventArgs e)
    {

        if (Session["UserId"] == null || Session["UserIdentity"].ToString() != "1")
        {
            Response.Redirect("Default.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }

    }
    /// <summary>
    /// 从DropDownList中获得所选信息，存储到SearchInfo数据结构里面去
    /// </summary>
    private void SetSearchInfo()
    {
        search[0].Province = DropDownList_pro1.SelectedItem.Text;
        search[0].Sex = DropDownList_sex1.SelectedItem.Text;
        search[0].Testyear = DropDownList_year1.SelectedItem.Text;
        search[0].Grade = DropDownList_grade1.SelectedItem.Text;
        search[0].Nationality = DropDownList_national1.SelectedItem.Text;
        search[0].Target = DropDownList_item1.SelectedItem.Text;
        search[0].College = DropDownList_major1.SelectedItem.Text;

        search[1].Province = DropDownList_pro2.SelectedItem.Text;
        search[1].Sex = DropDownList_sex2.SelectedItem.Text;
        search[1].Testyear = DropDownList_year2.SelectedItem.Text;
        search[1].Grade = DropDownList_grade2.SelectedItem.Text;
        search[1].Nationality = DropDownList_national2.SelectedItem.Text;
        search[1].Target = DropDownList_item2.SelectedItem.Text;
        search[1].College = DropDownList_major2.SelectedItem.Text;
    }
    /// <summary>
    /// 生成SQL查询语句
    /// </summary>
    /// <param name="groupnum">组别(0,1)</param>
    /// <returns>SQL查询语句</returns>
    private string CreateSqlString(int groupnum)
    {
        try
        {
            string sqlquery = "";
            string sqlString = "select UserId from UserDetailInfoView ";
            bool IsBegin = false;
            if (search[groupnum].Province != "全部")
            {
                sqlString += "where Location='" + search[groupnum].Province.Trim() + "' ";
                IsBegin = true;
            }
            if (search[groupnum].Nationality != "全部")
            {
                if (!IsBegin)
                {
                    sqlString += " where ";
                    IsBegin = true;
                }
                else sqlString += " and ";
                sqlString += "Nationality='" + search[groupnum].Nationality + "'";
            }
            if (search[groupnum].Grade != "全部")
            {
                if (!IsBegin)
                {
                    sqlString += " where ";
                    IsBegin = true;
                }
                else sqlString += " and ";
                sqlString += "grade='" + search[groupnum].Grade+ "'";
            }
            if (search[groupnum].Sex != "全部")
            {
                if (!IsBegin)
                {
                    sqlString += " where ";
                    IsBegin = true;
                }
                else sqlString += " and ";
                if (search[groupnum].Sex == "男")
                {
                    sqlString += "sex=0";
                }
                else sqlString += "sex=1";
            }
            if (search[groupnum].College!="全部院系")
            {
                if (!IsBegin)
                {
                    sqlString+=" where ";
                    IsBegin=true;
                }
                else sqlString+=" and ";
                sqlString+="CollegeName='"+search[groupnum].College+"'";
            }

            if (search[groupnum].Target=="身高体重指数")
            {
                sqlquery = "select count(UserId) from MarkAndLevel where TestDate='" + search[groupnum].Testyear + "-1-1' and UserId IN(";
                sqlquery+=sqlString+") and HeightWeightLevel=";
            }
            else if (search[groupnum].Target=="肺活量")
            {
                sqlquery = "select count(UserId) from MarkAndLevel where TestDate='" + search[groupnum].Testyear + "-1-1' and UserId IN(";
                sqlquery += sqlString + ") and VitalWeightLevel=";
            }
            else if (search[groupnum].Target == "全部")
            {
                sqlquery = "select count(UserId) from MarkAndLevel where TestDate='" + search[groupnum].Testyear + "-1-1' and UserId IN(";
                sqlquery += sqlString + ") and AllLevel=";
            }
            else
            {
                UserTest usertest = new UserTest();
                string catagory=usertest.GetCatagoryOfTestName(search[groupnum].Target);
                if (catagory == "耐力项目")
                {
                    sqlquery = "select count(UserId) from MarkAndLevel where TestDate='" + search[groupnum].Testyear + "-1-1' and UserId IN(";
                    if (!IsBegin)
                    {
                        IsBegin = true;
                        sqlString += " where ";
                    }
                    else sqlString += " and ";
                    sqlString += "UserId IN (select UserId from PhysiqueTest where TestName='" + search[groupnum].Target + "' and TestDate='" + search[groupnum].Testyear + "-1-1')";
                    sqlquery += sqlString + ") and StaminaLevel=";
                }
                else if (catagory == "速度、灵巧项目")
                {
                    sqlquery = "select count(UserId) from MarkAndLevel where TestDate='" + search[groupnum].Testyear + "-1-1' and UserId IN(";
                    if (!IsBegin)
                    {
                        IsBegin = true;
                        sqlString += " where ";
                    }
                    else sqlString += " and ";
                    sqlString += "UserId IN (select UserId from PhysiqueTest where TestName='" + search[groupnum].Target + "' and TestDate='" + search[groupnum].Testyear + "-1-1')";
                    sqlquery += sqlString + ") and SpeedLevel=";

                }
                else if (catagory == "柔韧、力量项目")
                {
                    sqlquery = "select Count(UserId) from MarkAndLevel where TestDate='" + search[groupnum].Testyear + "-1-1' and UserId IN(";
                    if (!IsBegin)
                    {
                        IsBegin = true;
                        sqlString += " where ";
                    }
                    else sqlString += " and ";
                    sqlString += "UserId IN (select UserId from PhysiqueTest where TestName='" + search[groupnum].Target + "' and TestDate='" + search[groupnum].Testyear + "-1-1')";
                    sqlquery += sqlString + ") and StrengthLevel=";
                }
            }
            return sqlquery;
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message + "\nSource:" + ex.Source + "\nTargetSite: " + ex.TargetSite.ToString() + "\nStackTrace:" + ex.StackTrace;
            Session["AtPage"] = "t_data_contrast.aspx";
            Response.Redirect("t_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return "";
        }

    }
    /// <summary>
    /// Level等级的数目
    /// </summary>
    /// <param name="sqlstring">查询sql语句</param>
    /// <param name="level">查询级别</param>
    /// <returns>该等级数目</returns>
    private int CountLevel(string sqlstring,string level)
    {
        try
        {
            SqlConnection connect = new SqlConnection("Data Source=Localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
            connect.Open();
            sqlstring += "'" + level + "'";
            SqlCommand command = new SqlCommand(sqlstring, connect);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                int count=int.Parse(reader.GetValue(0).ToString());
                reader.Dispose();
                connect.Close();
                return count;
            }
            connect.Close();
            return 0;
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message + "\nSource:" + ex.Source + "\nTargetSite: " + ex.TargetSite.ToString() + "\nStackTrace:" + ex.StackTrace+"\nSQL:"+sqlstring;
            Session["AtPage"] = "t_data_contrast.aspx";
            Response.Redirect("t_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return 0;
        }
    }
    /// <summary>
    /// 等级和数量的对应存储到search中去
    /// </summary>
    /// <param name="groupnum">组别（0，1）</param>
    /// <param name="FirstSqlString">查询语句</param>
    private void StoreLevelNum(int groupnum,string FirstSqlString)
    {
        try
        {
            int count = 0;
            if (search[groupnum].Target == "身高体重指数")
            {
                search[groupnum].LevelMemery["营养不良"] = CountLevel(FirstSqlString, "营养不良");
                count += search[groupnum].LevelMemery["营养不良"];
                search[groupnum].LevelMemery["较低体重"] = CountLevel(FirstSqlString, "较低体重");
                count += search[groupnum].LevelMemery["较低体重"];
                search[groupnum].LevelMemery["正常体重"] = CountLevel(FirstSqlString, "正常体重");
                count += search[groupnum].LevelMemery["正常体重"];
                search[groupnum].LevelMemery["超重"] = CountLevel(FirstSqlString, "超重");
                count += search[groupnum].LevelMemery["超重"];
                search[groupnum].LevelMemery["肥胖"] = CountLevel(FirstSqlString, "肥胖");
                count += search[groupnum].LevelMemery["肥胖"];
            }
            else
            {
                search[groupnum].LevelMemery["不及格"] = CountLevel(FirstSqlString, "不及格");
                count += search[groupnum].LevelMemery["不及格"];
                search[groupnum].LevelMemery["及格"] = CountLevel(FirstSqlString, "及格");
                count += search[groupnum].LevelMemery["及格"];
                search[groupnum].LevelMemery["良好"] = CountLevel(FirstSqlString, "良好");
                count += search[groupnum].LevelMemery["良好"];
                search[groupnum].LevelMemery["优秀"] = CountLevel(FirstSqlString, "优秀");
                count += search[groupnum].LevelMemery["优秀"];
            }
            search[groupnum].count = count;
            return;
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message + "\n Source:" + ex.Source + "\n TargetSite: " + ex.TargetSite.ToString() + "\nStackTrace:" + ex.StackTrace;
            Session["AtPage"] = "t_data_contrast.aspx";
            Response.Redirect("t_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    /// <summary>
    /// Button1点击事件响应
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            //从下拉选单中将数据提取出来。
            SetSearchInfo();

            //使用提取的搜索项，编写Sql搜索语句
            string FirstSqlString = CreateSqlString(0);
            string SecondSqlString = CreateSqlString(1);

            //将不同等级对应的数目存储到search中
            StoreLevelNum(0,FirstSqlString);
            StoreLevelNum(1, SecondSqlString);

            DrawBarColumn();
        }
        catch (Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message + "\n Source:" + ex.Source + "\n TargetSite: " + ex.TargetSite.ToString() + "\nStackTrace:" + ex.StackTrace;
            Session["AtPage"] = "t_data_contrast.aspx";
            Response.Redirect("t_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }

    /// <summary>
    /// 绘制柱状图，将对比结果显示出来。
    /// </summary>
    private void DrawBarColumn()
    {
        try
        {
            Series series1 = Chart1.Series.Add("第一组");
            Series series2 = Chart1.Series.Add("第二组");
            series1.Color = Color.FromArgb(230, Color.OrangeRed);
            series2.Color = Color.FromArgb(230, Color.Blue);

            series1.ChartType = SeriesChartType.Column;
            series2.ChartType = SeriesChartType.Column;

            foreach (KeyValuePair<string, int> element in search[0].LevelMemery)
            {
                DataPoint point = new DataPoint();
                point.SetValueXY(element.Key,Convert.ToSingle(element.Value)/Convert.ToSingle(search[0].count));
                point.ToolTip = element.Value.ToString()+"人";
                series1.Points.Add(point);
            }
            foreach (KeyValuePair<string, int> element in search[1].LevelMemery)
            {
                DataPoint point = new DataPoint();
                point.SetValueXY(element.Key, Convert.ToSingle(element.Value) / Convert.ToSingle(search[1].count));
                point.ToolTip = element.Value.ToString() + "人";
                series2.Points.Add(point);
            }

            Chart1.Titles[0].Text = "数据对比结果";

            Chart1.ChartAreas["ChartArea1"].AxisY.Title = "所占百分比";
            Chart1.ChartAreas["ChartArea1"].AxisY.LabelStyle.Format = "P02";
            // Show/Hide X axis end labels
            Chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.IsEndLabelVisible = false;

            // Set point width of the series
            Chart1.Series["第一组"]["PointWidth"] = "0.8";
            Chart1.Series["第二组"]["PointWidth"] = "0.8";

            Chart1.ChartAreas["ChartArea1"].Area3DStyle.IsRightAngleAxes = true;
            Chart1.ChartAreas["ChartArea1"].Area3DStyle.IsClustered = true;

            // Set chart area 3D rotation

            Chart1.ChartAreas["ChartArea1"].Area3DStyle.Inclination = 10;
            Chart1.ChartAreas["ChartArea1"].Area3DStyle.Rotation = 20;
            // Set legend style
            Chart1.Legends["Default"].LegendStyle = LegendStyle.Column;

            // Set legend docking
            Chart1.Legends["Default"].Docking = Docking.Right;

            // Set legend alignment
            Chart1.Legends["Default"].Alignment = StringAlignment.Center;

            Chart1.Legends["Default"].Enabled = true;

            Chart1.Series["第一组"].LegendToolTip = search[0].Province + "省份" + search[0].College + search[0].Grade + "年级在" + search[0].Testyear + "年测试" + search[0].Target + "项目" + search[0].Nationality + "民族" + search[0].Sex + "学生";
            Chart1.Series["第二组"].LegendToolTip = search[1].Province + "省份" + search[1].College + search[1].Grade + "年级在" + search[1].Testyear + "年测试" + search[1].Target + "项目" + search[1].Nationality + "民族" + search[1].Sex + "学生";
        }
        catch (Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message + "\n Source:" + ex.Source + "\n TargetSite: " + ex.TargetSite.ToString() + "\nStackTrace:" + ex.StackTrace;
            Session["AtPage"] = "t_data_contrast.aspx";
            Response.Redirect("t_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
}